from urllib import request
from django.shortcuts import render
    #连接mysql的类
import pymysql
    #引入项目的settings文件
from django.conf import settings
    # Create your views here.
import re


#-----这个是/主界面页面引用函数
def index(request):
    #     ""返回首页的页面"""
    #获取学生信息
    try:
        students = get_student()
        #正常展示在页面
        return render(request, 'index.html', {'students': students})
    except Exception as e:
        #如果连接出错，在错误页面展示信息
        error_info = "连接数据库出现异常，具体原因："+ str(e)
        return render(request,'error.html',{'msg':error_info})

#-----------------------这个是mysql数据库连接函数-------------------------------
def get_student():
    """获取数据库信息"""
    db_info = settings.DATABASES['mysql']
    #实例化一个连接
    mysql_conn = pymysql.connect(db_info['HOST'],db_info['USER'],db_info['PASSWORD'],db_info['DB'])
    #获取操作指针
    cursor = mysql_conn.cursor()
    #准备sql语句
    sql = "select * from user;"
    #执行
    try:
        #执行SQL语句
        cursor.execute(sql)
        #获取表所有信息
        results = cursor.fetchall()
        #返回
        return  results

    except Exception as e :
        raise e
        return ("错误的")






















#--------------------------------------分割线
#-----------------这个是/test02页面引用函数
def test(request1):#这个是做异常处理，调用下面这个函数，然后判断正常就返回，注意函数的参数，纠结了很久，然后再在urls。py里面去更改url路径和对应使用哪个函数，这个是最简单的连接sqlserver的,其他的我觉得太复杂，不学了，学一样连接方式就行了
    try:
        results = today_info()
        return render(request1,'test02.html',{'sql_server_results':results})
    except Exception as e:
        error_info = "连接数据库出现异常，具体原因：" + str(e)
        return render(request1, 'error.html', {'msg': error_info})








#-------------------------以下是sqlserver数据库连接的函数----------------------------------
def sql_server_get():#这个是连接数据库查询，然后然后结果，是一个查询函数
    import pymssql
    import re
    sql_server_conn = pymssql.connect(host='10.130.1.253', user='barcode', password='barcodefornexcom',
                                      database='MIS')
    sql_server_cursor = sql_server_conn.cursor()
    sql_server_cursor.execute('select iom01 from invoice_master; ')
    sql_server_resluts = sql_server_cursor.fetchall()  # 取出来的单个数据是元组，要进行格式化成字符串，然后去掉所有符号
    r = '[’!"#$%&\'()*+,-./:;<=>?@[\\]^_`{|}~\n。！，]+'  # 这个是多有需要替换的符号
    results1 = re.sub(r, '', str(sql_server_resluts[0]))  # 格式化成字符串后再把所有符号替换成''
    results2 = re.sub(r, '', str(sql_server_resluts[1]))
    results3 = re.sub(r, '', str(sql_server_resluts[2]))
    results4 = re.sub(r, '', str(sql_server_resluts[3]))
    sql_server_all = [results1, results2, results3, results4]
    return sql_server_all


def today_info():#查询当日在做的工单号，并且相关数据192.168.220.128

    import pymssql
    # 连接sqlserver数据库
    sql_server_conn = pymssql.connect(host='10.130.1.253', user='barcode', password='barcodefornexcom',
                                      database='MIS')
    # 查询出当天做的工单号
    sql_server_cursor = sql_server_conn.cursor()
    sql_server_cursor.execute(
        'select  form_id,form_no  from maf_master order by create_date desc ; ')  # 要看当日生产的哪个工单，只能利用这个时间来对比，今天在做的序号是哪个工单的，再看这个工单的数据
    aaa = sql_server_cursor.fetchone()
    # 保存用做查询的工单号
    form_no = aaa[1].strip()
    # 设置各工站的变量
    operate1 = "Q0015"
    operate2 = "Q0013"
    operate3 = "Q0016"
    operate4 = "Q0022"
    operate5 = "Q0020"
    # 完整的工单号码
    today_info = (aaa[0].strip() + '-' + aaa[1].strip())

    # 进行第一个工站的生产量查询
    sql_server_cursor1 = sql_server_conn.cursor()
    sql_server_cursor1.execute(
        "select count(*)number from maf_master where form_no='%s' and operator='%s' ;" % (form_no, operate1))
    bbb = str(list(sql_server_cursor1.fetchone())[0])
    print("正在生产工单：" + today_info)
    print("组装站：" + bbb)

    # 进行第二个工站的生产量查询
    sql_server_cursor2 = sql_server_conn.cursor()
    sql_server_cursor2.execute(
        "select count(*)number from maf_master where form_no='%s' and operator='%s';" % (form_no, operate2))
    ccc = str(list(sql_server_cursor2.fetchone())[0])
    print("基测站：" + ccc)

    # 进行第三个工站的生产量查询
    sql_server_cursor3 = sql_server_conn.cursor()
    sql_server_cursor3.execute(
        "select count(*)number from maf_master where form_no='%s' and operator='%s';" % (form_no, operate3))
    ddd = str(list(sql_server_cursor3.fetchone())[0])
    print("烧机站：" + ddd)

    # 进行第四个工站的生产量查询
    sql_server_cursor4 = sql_server_conn.cursor()
    sql_server_cursor4.execute(
        "select count(*)number from maf_master where form_no='%s' and operator='%s';" % (form_no, operate4))
    eee = str(list(sql_server_cursor4.fetchone())[0])
    print("功测站：" + eee)

    # 进行第五个工站的生产量查询
    sql_server_cursor5 = sql_server_conn.cursor()
    sql_server_cursor5.execute(
        "select count(*)number from maf_master where form_no='%s' and operator='%s';" % (form_no, operate5))
    fff = str(list(sql_server_cursor5.fetchone())[0])
    print("包装站：" + fff)

    all_info = [today_info, bbb, ccc, ddd, eee, fff]
    sql_server_conn.close()

    # 查询另外一个数据库中今天这个工单的计划生产量
    sql_server_conn_NEXCOM = pymssql.connect(host='10.130.1.253', user='barcode', password='barcodefornexcom',
                                             database='NEXCOM')
    sql_server_conn__NEXCOM_cursor = sql_server_conn_NEXCOM.cursor()
    sql_server_conn__NEXCOM_cursor.execute("select TA015 from MOCTA where TA002='%s' ;" % (form_no))
    today_info_allcount = int(list(sql_server_conn__NEXCOM_cursor.fetchone())[0])


    #完成率=包装站完成数量/总数量
    completion_rate = int(fff)/today_info_allcount
    #要把小数点换算成百分比
    completion_rate = "%.2f%%"%(completion_rate*100)


    #未完成量=总量-最后一个工站量
    today_info_weiwancheng = int(today_info_allcount)-int(fff)


    all_info.append(today_info_allcount)
    all_info.append(completion_rate)
    all_info.append(today_info_weiwancheng)
    print(all_info)
    return all_info










#画图函数
#主函数
def test_image(request1):#这个是做异常处理，调用下面这个函数，然后判断正常就返回，注意函数的参数，纠结了很久，然后再在urls。py里面去更改url路径和对应使用哪个函数，这个是最简单的连接sqlserver的,其他的我觉得太复杂，不学了，学一样连接方式就行了
    try:
        results = image()
        return render(request1,'test_image.html',{'sql_server_results':results})
    except Exception as e:
        error_info = "连接数据库出现异常，具体原因：" + str(e)
        return render(request1, 'error.html', {'msg': error_info})

def image():
    aaa = {"yiwancheng":220,"weiwancheng":430}
    return aaa
